package com.gemcoder.system.service;

import static com.gemcoder.system.datasource.JdbcTemplateUtil.executeSQL;
import java.util.ArrayList;
import java.util.List;
import java.util.HashMap;
import java.util.Map;
import org.springframework.stereotype.Service;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserName;
import static com.gemcoder.system.service.UserSdkService.sdkOfGetCurrentUserId;
import com.gemcoder.system.bo.Result;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllPermissions;
import static com.gemcoder.system.service.permission.PermissionSdkService.sdkOfgetAllMenus;
import java.sql.SQLException;
import org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder;
import cn.hutool.core.util.StrUtil;
import cn.hutool.db.Entity;
import cn.hutool.db.sql.SqlExecutor;
import cn.hutool.db.sql.SqlBuilder;
import cn.hutool.db.Db;

@Service
public class PersonnelschedulingService {

    public Result getSchedulingInfo(Map<String, Object> paramMap) {
        // 获取项目名称和月份
        String projectName = paramMap.get("ProjectNamevalue1") != null ? String.valueOf(paramMap.get("ProjectNamevalue1")) : null;
        String month = paramMap.get("Monthsvalue1") != null ? String.valueOf(paramMap.get("Monthsvalue1")) : null;
        // 检查项目名称和月份是否为空
        if (projectName == null || month == null) {
            return new Result(false, "请先选择一个项目和月份", null);
        }
        // 构建查询SQL
        StringBuilder sql = new StringBuilder("SELECT s.id, s.project_id, s.month, s.date, s.shift_type, s.day_shift_user_id, s.night_shift_user_id, s.mid_shift_user_id, s.create_time, s.update_time, s.create_username, s.update_username ");
        sql.append("FROM schedule s ");
        sql.append("JOIN project p ON s.project_id = p.id ");
        sql.append("WHERE p.project_name = ? AND s.month = ?");
        // 创建新的参数集合
        List<Object> params = new ArrayList<>();
        params.add(projectName);
        params.add(month);
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), params.toArray());
        // 返回结果
        return new Result(true, "查询成功", result);
    }

    public Result getShiftScheduling(Map<String, Object> paramMap) {
        // 获取项目名称和月份
        String projectName = paramMap.get("ProjectNamevalue1") != null ? String.valueOf(paramMap.get("ProjectNamevalue1")) : null;
        String month = paramMap.get("Monthsvalue1") != null ? String.valueOf(paramMap.get("Monthsvalue1")) : null;
        // 检查项目名称和月份是否为空
        if (projectName == null || month == null) {
            return new Result(false, "请先选择一个项目和月份", null);
        }
        // 查询项目ID
        String projectSql = "SELECT id FROM project WHERE project_name = ?";
        List<Map<String, Object>> projectResult = executeSQL(projectSql, projectName);
        if (projectResult.isEmpty()) {
            return new Result(false, "项目不存在", null);
        }
        Integer projectId = Integer.valueOf(String.valueOf(projectResult.get(0).get("id")));
        // 查询排班信息
        String scheduleSql = "SELECT id, project_id, month, date, shift_type, day_shift_user_id, night_shift_user_id, mid_shift_user_id, create_time, update_time, create_username, update_username FROM schedule WHERE project_id = ? AND month = ?";
        List<Map<String, Object>> scheduleResult = executeSQL(scheduleSql, projectId, month);
        // 返回结果
        Map<String, Object> resultData = new HashMap<>();
        resultData.put("total", scheduleResult.size());
        resultData.put("data", scheduleResult);
        return new Result(true, "查询成功", resultData);
    }

    public Result getAllUsers(Map<String, Object> paramMap) {
        // 构建查询所有用户的SQL
        StringBuilder sql = new StringBuilder("SELECT id, user_name, nick_name, email, status, phone_number, phone, remember_me, created_at, updated_at, name, mobile, create_username, create_time, update_username, update_time FROM users WHERE 1=1");
        // 创建一个新的集合来存储参数
        List<Object> params = new ArrayList<>();
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), params.toArray());
        // 构建返回结果
        Result res = new Result();
        res.setFlag(true);
        res.setMessage("查询成功");
        res.setData(result);
        return res;
    }

    public Result getAllUsersCVE(Map<String, Object> paramMap) {
        // 构建查询所有用户的SQL
        StringBuilder sql = new StringBuilder("SELECT id, user_name, nick_name, email, status, phone_number, phone, remember_me, created_at, updated_at, name, mobile, create_username, create_time, update_username, update_time FROM users WHERE 1=1");
        List<Object> params = new ArrayList<>();
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), params.toArray());
        // 构建返回结果
        Result res = new Result();
        res.setFlag(true);
        res.setMessage("查询成功");
        res.setData(result);
        return res;
    }

    public Result getAllUsers17V(Map<String, Object> paramMap) {
        // 构建查询所有用户的SQL
        StringBuilder sql = new StringBuilder("SELECT id, user_name, nick_name, email, status, phone_number, phone, remember_me, created_at, updated_at, name, mobile, create_username, create_time, update_username, update_time FROM users WHERE 1=1");
        List<Object> params = new ArrayList<>();
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString(), params.toArray());
        // 构建返回结果
        Result res = new Result();
        res.setFlag(true);
        res.setMessage("查询成功");
        res.setData(result);
        return res;
    }

    public Result syncLastMonthScheduling(Map<String, Object> paramMap) {
        // 获取参数
        String projectName = paramMap.get("ProjectNamevalue1") != null ? String.valueOf(paramMap.get("ProjectNamevalue1")) : null;
        String month = paramMap.get("Monthsvalue1") != null ? String.valueOf(paramMap.get("Monthsvalue1")) : null;
        // 检查是否选择了项目名称和月份
        if (projectName == null || month == null) {
            return new Result(false, "请先选择一个项目和月份", null);
        }
        // 获取当前登录者的名称
        String currentUserName = sdkOfGetCurrentUserName();
        // 获取项目ID
        StringBuilder projectSql = new StringBuilder("SELECT id FROM project WHERE project_name = ?");
        List<Map<String, Object>> projectResult = executeSQL(projectSql.toString(), projectName);
        if (projectResult.isEmpty()) {
            return new Result(false, "项目不存在", null);
        }
        Integer projectId = Integer.valueOf(String.valueOf(projectResult.get(0).get("id")));
        // 获取上一月份
        String lastMonth = month.substring(0, 4) + "-" + String.format("%02d", Integer.parseInt(month.substring(5)) - 1);
        // 查询上一月份的排班信息
        StringBuilder scheduleSql = new StringBuilder("SELECT project_id, month, date, shift_type, day_shift_user_id, night_shift_user_id, mid_shift_user_id, create_time, update_time, create_username, update_username FROM schedule WHERE project_id = ? AND month = ?");
        List<Map<String, Object>> scheduleResult = executeSQL(scheduleSql.toString(), projectId, lastMonth);
        // 复制排班信息到当前月份
        for (Map<String, Object> schedule : scheduleResult) {
            StringBuilder insertSql = new StringBuilder("INSERT INTO schedule (project_id, month, date, shift_type, day_shift_user_id, night_shift_user_id, mid_shift_user_id, create_time, update_time, create_username, update_username) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            executeSQL(insertSql.toString(), projectId, month, schedule.get("date"), schedule.get("shift_type"), schedule.get("day_shift_user_id"), schedule.get("night_shift_user_id"), schedule.get("mid_shift_user_id"), schedule.get("create_time"), schedule.get("update_time"), schedule.get("create_username"), currentUserName);
        }
        return new Result(true, "同步成功", null);
    }

    public Result getAllProjects(Map<String, Object> paramMap) {
        // 构建查询所有项目名称的SQL
        StringBuilder sql = new StringBuilder("SELECT project_name FROM project");
        // 执行SQL查询
        List<Map<String, Object>> result = executeSQL(sql.toString());
        // 构建返回结果
        Result response = new Result();
        response.setFlag(true);
        response.setMessage("查询成功");
        response.setData(result);
        return response;
    }
}
